package main;

import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.TreeSet;

import org.joda.time.DateTime;
import org.joda.time.Days;

import dao.Query;

public class EventHandler {
	private Query dbhandler;
	private static EventHandler gui;

	private EventHandler() {
		dbhandler = new Query();
		dbhandler.openConnection();
	}
	
	public static EventHandler getInstance() {
		if(gui == null)
			gui = new EventHandler();
	
		return gui;
	}

	private String combineString(String[] a) {
		String r = "'" + a[0] + "'";
		for (int i = 1; i < a.length; i++)
			r += ",'" + a[i] + "'";
		return r;
	}

	private String stripID(String param) {
		int rightb = param.indexOf(']');
		System.out.println(rightb);
		return param.substring(1, rightb);
	}

	// String[] customerParams = { "LastName", "FirstName", "PsWd", "email",
	// "0", "address","4038888888", "0" };
	public boolean createCustomer(String[] customerParams) {
		try {
			String query = new String("insert into Customer values(default,"
					+ combineString(customerParams) + ",default)");
			System.out.println("query:" + query);
			int ret = dbhandler.executeInsertStatement(query);
			if (ret == 0 || ret == -1)
				throw new Exception();
		} catch (Exception e) {
			System.err.println("create customer failed.");
			try {
				System.out.println("trying to update customer info");
				String updateQuery = new String(
						"update Customer set last_name='" + customerParams[0]
								+ "',first_name='" + customerParams[1]
								+ "',password='" + customerParams[2]
								+ "',address='" + customerParams[5]
								+ "',phone_num='" + customerParams[6]
								+ "',subscription='" + customerParams[7]
								+ "' where email='" + customerParams[3] + "'");
				System.out.println("updateQuery: " + updateQuery);
				dbhandler.executeUpdateStatement(updateQuery);
			} catch (Exception e1) {
				System.err.println("update customer info failed");
			}
			;
			return false;
		}
		return true;
	}

	/*************/
	public String[] getCustomer(String[] cp) {
		String query = new String(
				"select c.cid,c.first_name,c.last_name,c.type,c.address,c.phone_num from Customer c where c.email='"
						+ cp[0] + "' and c.password='" + cp[1] + "';");
		System.out.println("query:" + query);
		ResultSet r = dbhandler.executeSelectStatement(query);
		String[] rs = null;
		try {
			while (r.next()) {
				String[] temp = { r.getString(1), r.getString(2),
						r.getString(3), r.getString(4), r.getString(5),
						r.getString(6) };
				rs = temp;
			}
		} catch (SQLException e) {
			System.err.println("ERROR: Failed to read query result.");
		}
		return rs;
	}

	public boolean createHotel(String[] hotelParams) {
		boolean a = true;
		String name = hotelParams[0];
		String[] hotels = getHotels();
		int size = hotels.length;
		for (int i = 0; i < size; i++) {
			if (hotels[i].substring(hotels[i].indexOf(']') + 1).equals(name)) {
				a = false;
				break;
			} else
				a = true;
		}
		if (a == true) {
			String query = new String("insert into Hotel values(default,"
					+ combineString(hotelParams) + ");");
			System.out.println("query:" + query);
			dbhandler.executeInsertStatement(query);
		}
		return a;
	}

	public boolean createRoom(String[] roomParams) {
		boolean a = true;
		String[][] rooms = getRoom();
		int size = rooms.length;
		String number = roomParams[0];
		String hotel = roomParams[2].substring(roomParams[2].indexOf(']') + 1);
		String number1;
		String hotel1;
		for (int i = 0; i < size; i++) {
			number1 = rooms[i][1];
			hotel1 = rooms[i][3];
			if ((number.equals(number1)) && (hotel.equals(hotel1))) {
				a = false;
			}
		}
		if (a == true) {
			roomParams[1] = stripID(roomParams[1]);
			roomParams[2] = stripID(roomParams[2]);
			String query = new String("insert into Room values(default,"
					+ combineString(roomParams) + ");");
			System.out.println("query:" + query);
			dbhandler.executeInsertStatement(query);
		}
		return a;
	}

	public boolean createRoomType(String[] roomTypeParams) {
		boolean a = true;
		String name = roomTypeParams[0];
		String[] roomTypes = getRoomTypes();
		int size = roomTypes.length;
		for (int i = 0; i < size; i++) {
			if (roomTypes[i].substring(roomTypes[i].indexOf(']') + 1).equals(
					name)) {
				a = false;
				break;
			} else
				a = true;
		}
		if (a == true) {
			String query = new String("insert into Room_Type values(default,"
					+ combineString(roomTypeParams) + ");");
			System.out.println("query:" + query);
			dbhandler.executeInsertStatement(query);
		}
		return a;
	}

	public String[] getHotels() {
		String query = new String("select hid,name from Hotel;");
		ResultSet r = dbhandler.executeSelectStatement(query);
		TreeSet<String> s = new TreeSet<String>();
		try {
			while (r.next())
				s.add("[" + r.getString(1) + "]" + r.getString(2));
		} catch (SQLException e) {
			System.err.println("ERROR: Failed to read query result.");
		}

		return s.toArray(new String[0]);
	}

	public String[][] getAccount() {
		String query = new String(
				"select c.email,c.password,c.phone_num,c.type from Customer c;");
		System.out.println("query:" + query);
		ResultSet r = dbhandler.executeSelectStatement(query);
		ArrayList<String[]> s = new ArrayList<String[]>();
		try {
			while (r.next()) {
				String[] temp = { r.getString(1), r.getString(2),
						r.getString(3), r.getString(4) };
				s.add(temp);
			}
		} catch (SQLException e) {
			System.err.println("ERROR: Failed to read query result.");
		}

		return s.toArray(new String[0][0]);
	}

	public String[][] getHotelsDetail() {
		String query = new String("select * from Hotel;");
		ResultSet r = dbhandler.executeSelectStatement(query);
		ArrayList<String[]> s = new ArrayList<String[]>();
		try {
			while (r.next()) {
				String[] temp = { r.getString(1), r.getString(2),
						r.getString(3), r.getString(4), r.getString(5),
						r.getString(6) };
				s.add(temp);
			}
		} catch (SQLException e) {
			System.err.println("ERROR: Failed to read query result.");
		}

		return s.toArray(new String[0][0]);
	}

	public String[][] getRoom() {
		String query = new String(
				"select r.rid,r.number,rt.room_name,h.name from Room r,Room_Type rt,Hotel h where r.fk_roomtype = rt.rtid and r.fk_hotel = h.hid;");
		ResultSet r = dbhandler.executeSelectStatement(query);
		ArrayList<String[]> s = new ArrayList<String[]>();
		try {
			while (r.next()) {
				String[] temp = { r.getString(1), r.getString(2),
						r.getString(3), r.getString(4) };
				s.add(temp);
			}
		} catch (SQLException e) {
			System.err.println("ERROR: Failed to read query result.");
		}

		return s.toArray(new String[0][0]);
	}

	public String[] getRoomTypes() {
		String query = new String("select rtid,room_name from Room_Type;");
		ResultSet r = dbhandler.executeSelectStatement(query);
		TreeSet<String> s = new TreeSet<String>();
		try {
			while (r.next())
				s.add("[" + r.getString(1) + "]" + r.getString(2));
		} catch (SQLException e) {
			System.err.println("ERROR: Failed to read query result.");
		}

		return s.toArray(new String[0]);
	}

	public String[][] getRoomTypesDetail() {
		String query = new String("select * from Room_Type;");
		ResultSet r = dbhandler.executeSelectStatement(query);
		ArrayList<String[]> s = new ArrayList<String[]>();
		try {
			while (r.next()) {
				String[] temp = { r.getString(1), r.getString(2),
						r.getString(3), r.getString(4), r.getString(5) };
				s.add(temp);
			}
		} catch (SQLException e) {
			System.err.println("ERROR: Failed to read query result.");
		}

		return s.toArray(new String[0][0]);
	}

	public String[][] getBookingDetail(String hid, String status) {
		String end1 = "";
		if (status != null)
			end1 = " AND b.status ='" + status + "'";

		String end2 = "";
		if (hid != null)
			end2 = " AND h.hid = '" + hid + "'";

		String query = new String(
				"SELECT b.bid,c.first_name,c.last_name,c.email,r.number,b.date_in,b.date_out,b.total_cost,b.status,h.name "
						+ "FROM Booking b, Customer c, Room r, Hotel h "
						+ "WHERE b.fk_customer = c.cid AND b.fk_room = r.rid AND r.fk_hotel = h.hid "
						+ end1 + end2);
		System.out.println(query);
		ResultSet r = dbhandler.executeSelectStatement(query);
		ArrayList<String[]> s = new ArrayList<String[]>();
		try {
			while (r.next()) {
				String[] temp1 = { r.getString(1), r.getString(2),
						r.getString(3), r.getString(4), r.getString(5),
						r.getString(6), r.getString(7), r.getString(8),
						r.getString(9), r.getString(10) };
				s.add(temp1);
			}
		} catch (SQLException e) {
			System.err.println("ERROR: Failed to read query result.");
		}

		return s.toArray(new String[0][0]);
	}

	// input[0] -> hotelID
	// input[1] -> start_date
	// input[2] -> end_date
	public String[][] getAvailableRooms(String[] input) {
		// System.out.println("***"+input[0]);
		// System.out.println("***"+input[1]);
		// System.out.println("***"+input[2]);
		String query = new String(
				"SELECT r.rid,r.number,rt.room_name,h.name,rt.cost "
						+ "FROM Room r, Room_Type rt, Hotel h "
						+ "WHERE r.rid NOT IN "
						+ "(SELECT b.fk_room FROM Booking b "
						+ "WHERE b.date_in < '"
						+ input[2]
						+ "' AND b.date_out > '"
						+ input[1]
						+ "' AND (b.status = 'UNCONFIRMED' OR b.status = 'CONFIRMED' OR b.status = 'CHECKED_IN') ) "

						+ "AND r.fk_roomtype = rt.rtid AND h.hid = r.fk_hotel AND h.hid = '"
						+ input[0] + "'");
		System.out.println(query);
		ResultSet r = dbhandler.executeSelectStatement(query);
		ArrayList<String[]> s = new ArrayList<String[]>();
		try {
			while (r.next()) {
				String[] temp = { r.getString(1), r.getString(2),
						r.getString(3), r.getString(4), r.getString(5) };
				s.add(temp);
			}
		} catch (SQLException e) {
			System.err.println("ERROR: Failed to read query result.");
		}

		return s.toArray(new String[0][0]);
	}

	// input[0] -> customerID
	// input[1] -> roomID
	// input[2] -> start_date
	// input[3] -> end_date
	public boolean bookRoom(String[] input) {
		int numOfNights = EventHandler.daydiff(input[2], input[3]);
		double RoomCost = getRoomCost(input[1]) * numOfNights;
		try {
			String query = new String("insert into Booking values(default,'"
					+ numOfNights + "','" + input[2] + "','" + input[3] + "'"
					+ ",'" + RoomCost + "','" + RoomCost
					+ "','UNCONFIRMED',default,'" + input[0] + "','" + input[1]
					+ "',default);");
			System.out.println("query:" + query);
			dbhandler.executeInsertStatement(query);
		} catch (Exception e) {
			return false;
		}
		return true;
	}

	// input[0] -> booking id
	// input[1] -> cost
	// input[2] -> description
	public boolean addRoomService(String[] input) {
		try {
			String query = new String(
					"insert into Room_Service values(default,'" + input[1]
							+ "','" + input[2] + "','" + input[0] + "');");
			String updateQuery = new String(
					"update `Booking` b set b.total_cost = b.total_cost + '"
							+ input[1] + "' WHERE b.bid = '" + input[0] + "'");
			System.out.println("query:" + query);
			dbhandler.executeInsertStatement(query);
			dbhandler.executeUpdateStatement(updateQuery);
		} catch (Exception e) {
			return false;
		}
		return true;
	}

	public int getBookingID(String[] input) {
		String query = new String(
				"select b.bid from Booking b where b.fk_customer = '"
						+ input[0] + "' AND b.fk_room = '" + input[1]
						+ "' AND b.date_in = '" + input[2]
						+ "' AND b.date_out = '" + input[3] + "'");
		System.out.println("query:" + query);
		ResultSet r = dbhandler.executeSelectStatement(query);
		String rs = null;
		try {
			r.next();
			rs = r.getString(1);
		} catch (SQLException e) {
			System.err.println("ERROR: Failed to read query result.");
		}
		if (rs != null)
			return Integer.parseInt(rs);
		else
			return -1;
	}

	public boolean changeBookingStatus(String bid, String newStatus) {
		try {
			String query = new String("update Booking set status='" + newStatus
					+ "' where bid='" + bid + "';");
			System.out.println("query:" + query);
			if (dbhandler.executeInsertStatement(query) == 0)
				throw new Exception();
		} catch (Exception e) {
			System.err.println("confirm booking failed.");
			return false;
		}
		return true;
	}

	// account type 0-customer 1-emp 2-admin -1-invalid
	public int login(String email, String pswd) {
		String query = new String(
				"select c.type from Customer c where c.email='" + email
						+ "' and c.password='" + pswd + "';");
		System.out.println("query:" + query);
		ResultSet r = dbhandler.executeSelectStatement(query);
		String type = null;
		try {
			while (r.next()) {
				type = r.getString(1);
			}
			System.out.println("Type: " + type);
		} catch (SQLException e) {
			System.err.println("ERROR: Failed to read query result." + e);
		}
		if (type == null)
			return -1;
		else
			return Integer.parseInt(type);
	}

	public int getCustomerID(String email, String pswd) {
		String query = new String(
				"select c.cid from Customer c where c.email='" + email
						+ "' and c.password='" + pswd + "';");
		System.out.println("query:" + query);
		ResultSet r = dbhandler.executeSelectStatement(query);
		String type = null;
		try {
			while (r.next()) {
				type = r.getString(1);
			}
		} catch (SQLException e) {
			System.err.println("ERROR: Failed to read query result." + e);
		}
		if (type == null)
			return -1;
		else
			return Integer.parseInt(type);
	}

	public double getRoomCost(String rid) {
		String query = new String(
				"select rt.cost from Room_Type rt, Room r where r.fk_roomtype=rt.rtid and r.rid ='"
						+ rid + "';");
		System.out.println("query:" + query);
		ResultSet r = dbhandler.executeSelectStatement(query);
		String type = null;
		try {
			while (r.next()) {
				type = r.getString(1);
			}
		} catch (SQLException e) {
			System.err.println("ERROR: Failed to read query result." + e);
		}
		if (type == null)
			return -1;
		else
			return Double.parseDouble(type);
	}

	public String[][] getBills(String bid) {
		String query = new String(
				"select rs.description, rs.cost from Room_Service rs, Booking b where rs.fk_booking = b.bid and b.bid = '"
						+ bid + "';");
		System.out.println("query:" + query);
		ResultSet r = dbhandler.executeSelectStatement(query);
		ArrayList<String[]> s = new ArrayList<String[]>();
		try {
			while (r.next()) {
				String[] temp = { "Room Service", r.getString(1),
						r.getString(2) };
				s.add(temp);
			}
		} catch (SQLException e) {
			System.err.println("ERROR: Failed to read query result.");
		}

		String query1 = new String(
				"select b.room_cost, b.total_cost, b.amount_paid from Booking b where b.bid = '"
						+ bid + "';");
		System.out.println("query:" + query1);
		ResultSet r1 = dbhandler.executeSelectStatement(query1);
		try {
			while (r1.next()) {
				String[] temp = { "Room Cost", "Cost of using the room",
						r1.getString(1) };
				s.add(temp);
				s.add(new String[] { "Total", "Total Charge", r1.getString(2) });
				s.add(new String[] { "", "", "" });
				s.add(new String[] { "Amount Paid",
						"Amount customer already paid.", r1.getString(3) });
			}

		} catch (Exception e) {
			System.err.println("ERROR: Failed to read query result.");
		}

		return s.toArray(new String[0][0]);
	}

	public String getAmountPaid(String bid) {
		String query = new String(
				"select b.amount_paid from Booking b where b.bid='" + bid + "'");
		System.out.println("query:" + query);
		ResultSet r = dbhandler.executeSelectStatement(query);
		String rs = null;
		try {
			r.next();
			rs = r.getString(1);
		} catch (SQLException e) {
			System.err.println("ERROR: Failed to read query result.");
		}
		return rs;
	}

	public String getTotalCharge(String bid) {
		String query = new String(
				"select b.total_cost from Booking b where b.bid='" + bid + "'");
		System.out.println("query:" + query);
		ResultSet r = dbhandler.executeSelectStatement(query);
		String rs = null;
		try {
			r.next();
			rs = r.getString(1);
		} catch (SQLException e) {
			System.err.println("ERROR: Failed to read query result.");
		}
		return rs;
	}

	public void payBills(String bid, String amount) {
		try {
			String updateQuery = new String(
					"update Booking set amount_paid=amount_paid+'" + amount
							+ "' where bid='" + bid + "'");
			System.out.println("updateQuery: " + updateQuery);
			dbhandler.executeUpdateStatement(updateQuery);
		} catch (Exception e1) {
			System.err.println("update amount paid failed");
		}
	}

	public void startTest() {
		dbhandler.executeUpdateStatement("BEGIN;");
	}

	public void endTest() {
		dbhandler.executeUpdateStatement("ROLLBACK;");
	}

	public static long calculateDayDifference(Calendar startDate,
			Calendar endDate) {
		Calendar date = (Calendar) startDate.clone();
		long daysBetween = 0;
		while (date.before(endDate)) {
			date.add(Calendar.DAY_OF_MONTH, 1);
			daysBetween++;
		}
		return daysBetween;
	}

	public static DateTime createDate(String d1) {
		String[] fields = d1.split("-");
		return new DateTime(Integer.parseInt(fields[0]),
				Integer.parseInt(fields[1]), Integer.parseInt(fields[2]), 0, 0);
	}

	public static int daydiff(String d1, String d2) {
		DateTime dt1 = EventHandler.createDate(d1);
		DateTime dt2 = EventHandler.createDate(d2);
		try {
			return Days.daysBetween(dt1, dt2).getDays();
		} catch (Exception e) {
			return -1;
		}
	}

	public static void print(String[] r) {
		for (int i = 0; i < r.length; i++)
			System.out.print(r[i]);
		System.out.println();
	}

	public static void print(String[][] r) {
		for (int i = 0; i < r.length; i++) {
			for (int j = 0; j < r[0].length; j++)
				System.out.print(r[i][j] + ' ');
			System.out.println();
		}

	}

}
